<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class akuntansi extends CI_Controller {

    public function __construct() {
        parent::__construct();

        $this->load->library('auth');
        $this->load->library('base');
        $this->load->library('ajaxresponse');
        $this->load->library("phpexcel");
        $this->load->library("PHPExcel/iofactory");

        $this->load->model("report/m_akuntansi");
        $this->load->model("crud/m_info_usaha");
        $this->load->model("crud/m_info_lap_keuangan");
        $this->load->model("crud/m_perkiraan");
        $this->load->model("crud/m_history_saldo");
    }

    function index() {
        $this->jurnal();
    }

    function jurnal(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Akuntansi');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/akuntansi/jurnal', $data);
    }

    function bukubesar(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Akuntansi');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/akuntansi/bukubesar', $data);
    }

    function neracasaldo(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Akuntansi');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/akuntansi/neracasaldo', $data);
    }

    function rugilaba(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Akuntansi');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/akuntansi/rugilaba', $data);
    }

    function neraca(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Akuntansi');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/akuntansi/neraca', $data);
    }

    function sum_neracasaldo(){
        $this->auth->is_logged_in('form/login');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $saldoawal = $this->m_perkiraan->get();

        foreach($saldoawal->result() as $row){
            $perkiraan = $row->KODE_PERKIRAAN;
            $saldoawal = $row->SALDO;

            $result = $this->m_akuntansi->sum_bukubesar($date,$perkiraan);

            foreach($result->result() as $sum){
                if ($sum->KODE == 'D'){
                    $saldoawal+=$sum->JUMLAH;
                }else if ($sum->KODE == 'K'){
                    $saldoawal-=$sum->JUMLAH;
                }
            }
            $this->m_perkiraan->update_saldo($perkiraan,$saldoawal);
        }
    }

    function update_saldo($perkiraan){
        $this->auth->is_logged_in('form/login');

        $saldoawal = $this->m_perkiraan->get_one($perkiraan);
        $row = $saldoawal->row();

        $saldoawal = $row->SALDO;
        $result = $this->m_akuntansi->sum_bukubesar($date,$perkiraan);

        foreach($result->result() as $sum){
            if ($sum->KODE == 'D'){
                $saldoawal+=$sum->JUMLAH;
            }else if ($sum->KODE == 'K'){
                $saldoawal-=$sum->JUMLAH;
            }
        }
        $this->m_history_saldo->update_saldo($perkiraan,$saldoawal);
    }


    function calc_neracasaldo()
    {
        $date = $this->input->post("TGL_NERACASALDO");
        $datestring = "%d-%m-%Y";
        $todate = mdate($datestring, time());
        //$result = $this->m_akuntansi->sum_neracasaldo($date);
        
        // --------------------------- create excel ---------------------------------//
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("Neraca Saldo")
                ->setDescription("description");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);

        $objPHPExcel->getActiveSheet()->getStyle('A8')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A8'), 'B8:E8' );

        $objPHPExcel->getActiveSheet()->setCellValue('A1', "KOPERASI KARYAWAN TOTAL INDONESIE");
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "JL.JEND.SUDIRMAN H2 NO.5-6 BALIKPAPAN PERMAI");
        $objPHPExcel->getActiveSheet()->setCellValue('A3', "BALIKPAPAN");
        $objPHPExcel->getActiveSheet()->setCellValue('A4', "Tlp.: 0542-427723, Fac.: 0542-427722");
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->mergeCells('A1:E1');
        $sheet->mergeCells('A2:E2');
        $sheet->mergeCells('A3:E3');
        $sheet->mergeCells('A4:E4');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A6', "JURNAL BULAN : $date");
        $objPHPExcel->getActiveSheet()->setCellValue('E6', "TANGGAL : $todate");

        $objPHPExcel->getActiveSheet()->setCellValue('A8', "No");
        $objPHPExcel->getActiveSheet()->setCellValue('B8', "Nomer Perkiraan");
        $objPHPExcel->getActiveSheet()->setCellValue('C8', "Nama Perkiraan");
        $objPHPExcel->getActiveSheet()->setCellValue('D8', "Jumlah Debet");
        $objPHPExcel->getActiveSheet()->setCellValue('E8', "Jumlah Kredit");
        
        $dperkiraan = $this->m_perkiraan->get();
        $i = 9;
        foreach($dperkiraan->result() as $row){
            $perkiraan = $row->KODE_PERKIRAAN;
            $hsaldo = $this->m_history_saldo->get("KODE_PERKIRAAN = $perkiraan and TGL_CATAT like '$date%'");

            $saldoawal = 0;
            if ($hsaldo->num_rows() > 0){
                $saldo = $hsaldo->row();
                $saldoawal = $saldo->SALDO_AWAL;
                }

                $result = $this->m_akuntansi->sum_bukubesar($date,$perkiraan);

                foreach($result->result() as $sum){
                    if ($sum->KODE == 'D'){
                        $saldoawal+=$sum->JUMLAH;
                    }else if ($sum->KODE == 'K'){
                        $saldoawal-=$sum->JUMLAH;
                    }
                 }

        // update nilai saldo
        $year = date("Y", strtotime($date));
        $month = (date("m", strtotime($date))+1);
        if ($month<10) $month = "0".$month;
        else if ($month>12) {$year += 1;$month = "01";}
        $this->m_history_saldo->update_saldo($perkiraan,$saldoawal,$year.'-'.$month.'-01');

                if ($saldoawal!=0){
                    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i - 8);
                    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row->KODE_PERKIRAAN);
                    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $row->URAIAN_PERKIRAAN);
                    if ($saldoawal>0)
                        $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $saldoawal);
                    else
                        $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $saldoawal);
                    $i++;
                }
           
        }
        
        $objPHPExcel->getActiveSheet()->setCellValue("D$i", "=SUM(D8:D".($i-1).")");
        $objPHPExcel->getActiveSheet()->setCellValue("E$i", "=SUM(E8:E".($i-1).")");
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLaporan Neraca Saldo\n!');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

        // Save it as an excel 2003 file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Laporan Neraca Saldo.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }


    function calc_bukubesar(){
        $date = $this->input->post("TGL_BUKUBESAR");
        $perkiraan = $this->input->post("KODE_PERKIRAAN");
        $datestring = "%d-%m-%Y";
        $todate = mdate($datestring, time());
        
        $det_perk = $this->m_perkiraan->get_one($perkiraan)->row();
        $result = $this->m_akuntansi->sum_bukubesar($date,$perkiraan);

        $hsaldo = $this->m_history_saldo->get("KODE_PERKIRAAN = $perkiraan and TGL_CATAT like '$date%'");

            

        // --------------------------- create excel ---------------------------------//
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("Buku Besar")->setDescription("description");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);


        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);

        $objPHPExcel->getActiveSheet()->getStyle('A8')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A8'), 'B8:G8' );

        $objPHPExcel->getActiveSheet()->setCellValue('A1', "KOPERASI KARYAWAN TOTAL INDONESIE");
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "JL.JEND.SUDIRMAN H2 NO.5-6 BALIKPAPAN PERMAI");
        $objPHPExcel->getActiveSheet()->setCellValue('A3', "BALIKPAPAN");
        $objPHPExcel->getActiveSheet()->setCellValue('A4', "Tlp.: 0542-427723, Fac.: 0542-427722");
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->mergeCells('A1:G1');
        $sheet->mergeCells('A2:G2');
        $sheet->mergeCells('A3:G3');
        $sheet->mergeCells('A4:G4');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A6', "JURNAL BULAN : $date");
        $objPHPExcel->getActiveSheet()->setCellValue('G6', "TANGGAL : $todate");
        $objPHPExcel->getActiveSheet()->setCellValue('A7', "Perkiraan : $perkiraan (".$det_perk->URAIAN_PERKIRAAN.")");

        $objPHPExcel->getActiveSheet()->setCellValue('A8', "No");
        $objPHPExcel->getActiveSheet()->setCellValue('B8', "Tanggal");
        $objPHPExcel->getActiveSheet()->setCellValue('C8', "Nomer Transaksi");
        $objPHPExcel->getActiveSheet()->setCellValue('D8', "Keterangan");
        $objPHPExcel->getActiveSheet()->setCellValue('E8', "Debet");
        $objPHPExcel->getActiveSheet()->setCellValue('F8', "Kredit");
        $objPHPExcel->getActiveSheet()->setCellValue('G8', "Saldo");

        // create first row
        $saldoawal = 0;
        $i = 9;
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, 1);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $date."-01");
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, '-');
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, 'Saldo Awal');
        if ($hsaldo->num_rows() > 0){
                $saldo = $hsaldo->row();
                $saldoawal = $saldo->SALDO_AWAL;
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $saldo->SALDO_AWAL);
        }else {
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, 0);
        }
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, "-");
        $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, "=E$i");

        ///
        
        // next row
        $result = $this->m_akuntansi->sum_bukubesar($date,$perkiraan);
        $notrx = 0;
        foreach($result->result() as $row) {

                if ($row->KODE == 'D'){
                        $saldoawal+=$row->JUMLAH;
                    }else if ($row->KODE == 'K'){
                        $saldoawal-=$row->JUMLAH;
                    }
                
                $i++;
                $j = $i-1;
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i-8);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row->TGL_TRANSAKSI);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $row->NOMER_TRANSAKSI);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $row->KETERANGAN);
                if ($row->KODE=='D'){
                    $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $row->JUMLAH);
                    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, "=G$j+E$i");
                }
                else if ($row->KODE=='K'){
                    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $row->JUMLAH);
                    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, "=G$j-F$i");
                }
        }

        // update nilai saldo
        $year = date("Y", strtotime($date));
        $month = (date("m", strtotime($date))+1);
        if ($month<10) $month = "0".$month;
        else if ($month>12) {$year += 1;$month = "01";}
        $this->m_history_saldo->update_saldo($perkiraan,$saldoawal,$year.'-'.$month.'-01');
        
        //$objPHPExcel->getActiveSheet()->setCellValue("E$i", "=SUM(E2:E".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("F$i", "=SUM(F2:F".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("G$i", "=SUM(G2:G".$i.")");
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLaporan Buku Besar\n!');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

        // Save it as an excel 2003 file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Laporan Buku Besar.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');

    }

    function calc_jurnal(){
        $date = $this->input->post("TGL_JURNAL");
        $result = $this->m_akuntansi->sum_jurnal($date);
        $datestring = "%d-%m-%Y";
        $todate = mdate($datestring, time());

        // --------------------------- create excel ---------------------------------//
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("Jurnal")->setDescription("description");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(false);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);


        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);

        $objPHPExcel->getActiveSheet()->getStyle('A8')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A8'), 'B8:H8' );

        $objPHPExcel->getActiveSheet()->setCellValue('A1', "KOPERASI KARYAWAN TOTAL INDONESIE");
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "JL.JEND.SUDIRMAN H2 NO.5-6 BALIKPAPAN PERMAI");
        $objPHPExcel->getActiveSheet()->setCellValue('A3', "BALIKPAPAN");
        $objPHPExcel->getActiveSheet()->setCellValue('A4', "Tlp.: 0542-427723, Fac.: 0542-427722");
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->mergeCells('A1:H1');
        $sheet->mergeCells('A2:H2');
        $sheet->mergeCells('A3:H3');
        $sheet->mergeCells('A4:H4');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        
        $objPHPExcel->getActiveSheet()->setCellValue('A6', "JURNAL BULAN : $date");
        $objPHPExcel->getActiveSheet()->setCellValue('H6', "TANGGAL : $todate");


        $objPHPExcel->getActiveSheet()->setCellValue('A8', "No");
        $objPHPExcel->getActiveSheet()->setCellValue('B8', "Tanggal");
        $objPHPExcel->getActiveSheet()->setCellValue('C8', "Nomer Transaksi");
        $objPHPExcel->getActiveSheet()->setCellValue('D8', "No.Perkiraan");
        $objPHPExcel->getActiveSheet()->setCellValue('E8', "Perkiraan");
        $objPHPExcel->getActiveSheet()->setCellValue('F8', "Debet");
        $objPHPExcel->getActiveSheet()->setCellValue('G8', "Kredit");
        $objPHPExcel->getActiveSheet()->setCellValue('H8', "Keterangan");

        $i = 8;
        $j = 0;
        $notrx = '0';
        $kperkiraan = 0;
        $jperkiraan = 0;
        foreach($result->result() as $row) {
            $i++;
            if ($notrx!=$row->NOMER_TRANSAKSI){
                $j++;
                $notrx = $row->NOMER_TRANSAKSI;
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $j);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row->TGL_TRANSAKSI);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $row->NOMER_TRANSAKSI);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $row->ID_PERKIRAAN);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $row->URAIAN_PERKIRAAN);
                if ($row->KODE=='D'){
                    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $row->JUMLAH);
                }
                else if ($row->KODE=='K'){
                    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $row->JUMLAH);
                }
                $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $row->KETERANGAN);
            }else
            {
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $row->ID_PERKIRAAN);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $row->URAIAN_PERKIRAAN);
                if ($row->KODE=='D'){
                    $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $row->JUMLAH);
                }
                else if ($row->KODE=='K'){
                    $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $row->JUMLAH);
                }
                //$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $row->KETERANGAN);
            }
        }
        //$objPHPExcel->getActiveSheet()->setCellValue("E$i", "=SUM(E2:E".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("F$i", "=SUM(F2:F".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("G$i", "=SUM(G2:G".$i.")");
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLaporan Jurnal\n!');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

        // Save it as an excel 2003 file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Laporan Jurnal.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    function laporan_rugilaba(){
        $date = $this->input->post("TGL_RUGILABA");
        $datestring = "%d-%m-%Y";
        $todate = mdate($datestring, time());
        $year = date("Y", strtotime($date));
        $month = date("m", strtotime($date));

        /////////////////////////// inisiasi dok
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->getProperties()->setTitle("Laporan Rugi Laba")->setDescription("description");

            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(false);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

            $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
            $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
            $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
            $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);

            $objPHPExcel->getActiveSheet()->getStyle('A8')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
            $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A8'), 'B8:E8' );

            $objPHPExcel->getActiveSheet()->setCellValue('A1', "KOPERASI KARYAWAN TOTAL INDONESIE");
            $objPHPExcel->getActiveSheet()->setCellValue('A2', "JL.JEND.SUDIRMAN H2 NO.5-6 BALIKPAPAN PERMAI");
            $objPHPExcel->getActiveSheet()->setCellValue('A3', "BALIKPAPAN");
            $objPHPExcel->getActiveSheet()->setCellValue('A4', "Tlp.: 0542-427723, Fac.: 0542-427722");
            $sheet = $objPHPExcel->getActiveSheet();
            $sheet->mergeCells('A1:E1');
            $sheet->mergeCells('A2:E2');
            $sheet->mergeCells('A3:E3');
            $sheet->mergeCells('A4:E4');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //////////////////////////// end inisiasi doc

            $objPHPExcel->getActiveSheet()->setCellValue('A7', "A. Pendapatan Kotor");
            $sheet->mergeCells('A7:C7');
            $objPHPExcel->getActiveSheet()->getStyle('A7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
            $objPHPExcel->getActiveSheet()->setCellValue('A8', "No");
            $objPHPExcel->getActiveSheet()->setCellValue('B8', "Urut");
            $objPHPExcel->getActiveSheet()->setCellValue('C8', "Keterangan");
            $objPHPExcel->getActiveSheet()->setCellValue('D8', "Jumlah");
            $objPHPExcel->getActiveSheet()->setCellValue('E8', "Total");

        $index = 9;
        $number = 1;
        $biaya = false;
        $nobiaya = 0;
        $listformula = $this->m_info_lap_keuangan->get("JENIS = 2");
        foreach ($listformula->result() as $formula){
            $formulasi = $formula->FORMULASI_PERHITUNGAN;
            $total = 0;
            $operand = '+';

            for ($i = 0;$i<strlen($formulasi);$i+=5){
                $perkiraan = substr($formulasi,$i,5);

                for ($m=1;$m<=$month;$m++){
                    if ($m<10) $mm = "0".$m;
                    
                    $query = $this->m_history_saldo->get("KODE_PERKIRAAN = '$perkiraan' and TGL_CATAT like '$year-$mm%'");
                    if($query->num_rows() > 0){
                            if ($operand=='+'){
                                $total += $query->row()->SALDO_AWAL;
                            }else $total-=$query->row()->SALDO_AWAL;
                        }
                }
                $operand = substr($formulasi,$i+5,$i+6);
                $i++;
            }

            if ($formula->URUT >= 200 && !$biaya){
                $biaya = true;

                $objPHPExcel->getActiveSheet()->setCellValue("A$index", "Jumlah Pendapatan Kotor : ");
                $sheet->mergeCells("A$index:C$index");
                $objPHPExcel->getActiveSheet()->getStyle("A$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->setCellValue("E$index", "=SUM(D9:D".($index-1).")");

                
                $index+=2;
                $objPHPExcel->getActiveSheet()->setCellValue("A$index", "B. Biaya");
                $sheet->mergeCells("A$index:C$index");
                $objPHPExcel->getActiveSheet()->getStyle("A$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $index++;
                $objPHPExcel->getActiveSheet()->getStyle("A$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle("A$index")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
                $objPHPExcel->getActiveSheet()->getStyle("A$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle("A$index")->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle("A$index"), "B$index:E$index" );

                $objPHPExcel->getActiveSheet()->setCellValue("A$index", "No");
                $objPHPExcel->getActiveSheet()->setCellValue("B$index", "Urut");
                $objPHPExcel->getActiveSheet()->setCellValue("C$index", "Keterangan");
                $objPHPExcel->getActiveSheet()->setCellValue("D$index", "Jumlah");
                $objPHPExcel->getActiveSheet()->setCellValue("E$index", "Total");
                $index++;
            }

            $nobiaya = $index;

            // jadikan dokumen
                //////////////////////////////////////////////////////////////
            ///////////////////////////////////////////////////////////////
            if ($total != 0 )
                {
                $objPHPExcel->getActiveSheet()->setCellValue("A$index", $number);
                $objPHPExcel->getActiveSheet()->setCellValue("B$index", $formula->URUT);
                $objPHPExcel->getActiveSheet()->setCellValue("C$index", $formula->URAIAN_PERKIRAAN);
                $objPHPExcel->getActiveSheet()->setCellValue("D$index", $total);
                $objPHPExcel->getActiveSheet()->setCellValue("E$index", '');
                $index++;
                $number++;
            }
        }

        $objPHPExcel->getActiveSheet()->setCellValue("A$index", "Jumlah Biaya : ");
        $sheet->mergeCells("A$index:C$index");
        $objPHPExcel->getActiveSheet()->getStyle("A$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValue("E$index", "=SUM(D$nobiaya:D".($index-1).")");
        $index++;

        $objPHPExcel->getActiveSheet()->setCellValue("A$index", "Laba Sebelum Pajak : ");
        $sheet->mergeCells("A$index:C$index");
        $objPHPExcel->getActiveSheet()->getStyle("A$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValue("E$index", "=E".($nobiaya-4)."-E".($index-1));
        $index++;

        $objPHPExcel->getActiveSheet()->setCellValue("A$index", "Sisa Hasil Usaha : ");
        $sheet->mergeCells("A$index:C$index");
        $objPHPExcel->getActiveSheet()->getStyle("A$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValue("E$index", "=E".($index-1));
        $index++;

        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLaporan Rugi Laba\n!');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

        // Save it as an excel 2003 file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Laporan Rugi Laba.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        
        
    }
    
    function laporan_neraca(){
        
    }

}
?>
